package com.syxp.pda.dao.detail.impl;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.syxp.pda.core.bean.FieldBean;
import com.syxp.pda.core.command.QueryFilter;
import com.syxp.pda.core.dao.impl.BaseDaoImpl;
import com.syxp.pda.core.web.PagingBean;
import com.syxp.pda.dao.detail.CheckPlanDetailDao;
import com.syxp.pda.entity.detail.CheckPlanDetail;

public class CheckPlanDetailDaoImpl extends BaseDaoImpl<CheckPlanDetail>
		implements CheckPlanDetailDao {

	public CheckPlanDetailDaoImpl() {
		super(CheckPlanDetail.class);
	}

	@SuppressWarnings("unchecked")
	public List<CheckPlanDetail> getAll(final String entityName) {
		// TODO Auto-generated method stub
		return (List<CheckPlanDetail>) getHibernateTemplate().execute(
				new HibernateCallback() {

					@Override
					public Object doInHibernate(Session arg0)
							throws HibernateException, SQLException {
						Query query = getSession().createQuery(
								"from " + entityName);
						return query.list();
					}
				});
	}

	@SuppressWarnings("unchecked")
	public List getAll(final PagingBean paramPagingBean, final String entityName) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session arg0)
					throws HibernateException, SQLException {
				Query query = getSession().createQuery("from " + entityName)
						.setFirstResult(paramPagingBean.getStart())
						.setMaxResults(paramPagingBean.getPageSize());
				return query.list();
			}

		});
	}

	@SuppressWarnings("unchecked")
	public List getAll(final QueryFilter paramQueryFilter,
			final String entityName) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session arg0)
					throws HibernateException, SQLException {
				Criteria criteria = DetachedCriteria.forEntityName(entityName)
						.getExecutableCriteria(arg0);
				return setQueryByCriteria(criteria, paramQueryFilter);
			}
		});
	}

	@Override
	public CheckPlanDetail save(CheckPlanDetail paramT, String entityName) {
		getHibernateTemplate().save(entityName, paramT);
		return paramT;
	}

	@Override
	public CheckPlanDetail update(CheckPlanDetail paramT, String entityName) {
		getHibernateTemplate().update(entityName, paramT);
		return null;
	}

	@Override
	public void remove(CheckPlanDetail paramT, String entityName) {
		getHibernateTemplate().delete(entityName, paramT);
	}

	public void remove(Integer paramPK, String entityName) {
		remove((CheckPlanDetail) getHibernateTemplate()
				.get(entityName, paramPK));

	}

	@Override
	public CheckPlanDetail get(Integer paramPK, String entityName) {
		return (CheckPlanDetail) getHibernateTemplate()
				.get(entityName, paramPK);
	}

	@Override
	public List<Object[]> getReusltTrend(int checkStandardID, String startDate,
			String endDate, String precision) {
		return (List<Object[]>) getSession()
				.createSQLQuery(
						"select DevName,DateAdd("+precision+", DateDiff("+precision+", 0, PDA_CHKDateTime), 0)  AS PDA_CHKDateTime,"
								+ "Round(Avg(CHKResultValue), 2) AS CHKResultValue FROM EL_CHKPlanDetail WHERE CHKStandardID = ? and CHKResultValue IS NOT NULL"
								+ " and IsDownload = 0 and PDA_CHKDateTime >=? and PDA_CHKDateTime < ? "
								+ "GROUP BY DevName, DateAdd("+precision+", DateDiff("+precision+", 0, PDA_CHKDateTime), 0) ORDER BY DevName ASC, PDA_CHKDateTime ASC")
				.setInteger(0, checkStandardID).setString(1, startDate)
				.setString(2, endDate).list();
	}

	@Override
	public List<Object[]> getPointsAnalysis(Integer deviceIDs[],
			String chkItem, String startDate, String endDate,String precision) {
		return (List<Object[]>) getSession()
				.createSQLQuery(
						"SELECT DevName, "
								+ "DateAdd("+precision+", DateDiff("+precision+", 0, PDA_CHKDateTime), 0) AS PDA_CHKDateTime, "
								+ "Round(Avg(CHKResultValue), 2) AS CHKResultValue FROM EL_CHKPlanDetail "
								+ "WHERE DeviceInfoID in (:deviceIDs) and CHKItem = :item and "
								+ "CHKResultValue IS NOT NULL and IsDownload = 0 and PDA_CHKDateTime >= :startDate "
								+ "and PDA_CHKDateTime < :endDate "
								+ "GROUP BY DevName, DateAdd("+precision+", DateDiff("+precision+", 0, PDA_CHKDateTime), 0) "
								+ "ORDER BY DevName ASC, PDA_CHKDateTime ASC")
				.setParameterList("deviceIDs", deviceIDs)
				.setParameter("item", chkItem)
				.setParameter("startDate", startDate)
				.setParameter("endDate", endDate).list();
	}

	public List<Object[]> excuteSql(String sqlStr, List<FieldBean> field) {
		SQLQuery sq = getSession().createSQLQuery(sqlStr);
		for (int i = 0; i < field.size(); i++) {
			if (field.get(i).getValue() instanceof String) {
				sq.setString(i, (String) field.get(i).getValue());
			} else if (field.get(i).getValue() instanceof Integer) {
				sq.setInteger(i, (Integer) field.get(i).getValue());
			} else if (field.get(i).getValue() instanceof Date) {
				sq.setDate(i, (Date) field.get(i).getValue());
			}
		}

		return (List<Object[]>) sq.list();
	}

}
